﻿namespace OleDbDAL.Guest
{
    using DBUtility;
    using IDal.Guest;
    using Model.Guest;
    using Model.Systems;
    using System;
    using System.Data;
    using System.Text;
    using WinFormSession;

    public class GuestApp : IGuest
    {
        private string dbname = "Systems";

        public void Add(GuestEnt model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into T_Guest(");
            builder.Append("ChainNum,GuestTypes,GuestTypeId,GuestName,PayType,BankName,BankNum,Birthday,Sex,Addresses,Tel,TIN,ClearingType,MaxMoney,OtherTel,Remark,CreateMan,UpdateTime,CumulativeMoney,PaidMoney,OutstMoney,IsPrivacy,WangWangNum,ConsumeNum");
            builder.Append(")");
            builder.Append(" values (");
            builder.Append("" + model.ChainNum + ",");
            builder.Append("" + model.GuestTypes + ",");
            builder.Append("" + model.GuestTypeId + ",");
            builder.Append("'" + model.GuestName + "',");
            builder.Append("" + model.PayType + ",");
            builder.Append("'" + model.BankName + "',");
            builder.Append("'" + model.BankNum + "',");
            builder.Append("'" + model.Birthday + "',");
            builder.Append("" + model.Sex + ",");
            builder.Append("'" + model.Addresses + "',");
            builder.Append("'" + model.Tel + "',");
            builder.Append("'" + model.TIN + "',");
            builder.Append("" + model.ClearingType + ",");
            builder.Append("" + model.MaxMoney + ",");
            builder.Append("'" + model.OtherTel + "',");
            builder.Append("'" + model.Remark + "',");
            builder.Append("" + model.CreateMan + ",");
            builder.Append("'" + model.UpdateTime + "',");
            builder.Append("" + model.CumulativeMoney + ",");
            builder.Append("" + model.PaidMoney + ",");
            builder.Append("" + model.OutstMoney + ",");
            builder.Append("" + model.IsPrivacy + ",");
            builder.Append("'" + model.WangWangNum + "',");
            builder.Append("" + model.ConsumeNum + "");
            builder.Append(")");
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }

        public void Delete(int GuestId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from T_Guest ");
            builder.Append(" where GuestId=" + GuestId);
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }

        public DataTable GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from T_Guest ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by GuestId ");
            return DbHelperOledb.Query(this.dbname, builder.ToString());
        }

        public int GetMaxId(string swhere)
        {
            return DbHelperOledb.GetMaxID(this.dbname, "GuestId", "T_Guest", swhere);
        }

        public GuestEnt GetModel(int GuestId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select GuestId,ChainNum,GuestTypes,GuestTypeId,GuestName,PayType,BankName,BankNum,Birthday,Sex,Addresses,Tel,TIN,ClearingType,MaxMoney,OtherTel,Remark,CreateMan,UpdateTime,CumulativeMoney,PaidMoney,OutstMoney,IsPrivacy,WangWangNum,ConsumeNum from T_Guest ");
            builder.Append(" where GuestId=" + GuestId);
            GuestEnt ent = new GuestEnt();
            DataTable table = DbHelperOledb.Query(this.dbname, builder.ToString());
            ent.GuestId = GuestId;
            if (table.Rows.Count > 0)
            {
                if (table.Rows[0]["ChainNum"].ToString() != "")
                {
                    ent.ChainNum = int.Parse(table.Rows[0]["ChainNum"].ToString());
                }
                if (table.Rows[0]["GuestTypes"].ToString() != "")
                {
                    ent.GuestTypes = int.Parse(table.Rows[0]["GuestTypes"].ToString());
                }
                if (table.Rows[0]["GuestTypeId"].ToString() != "")
                {
                    ent.GuestTypeId = int.Parse(table.Rows[0]["GuestTypeId"].ToString());
                }
                ent.GuestName = table.Rows[0]["GuestName"].ToString();
                if (table.Rows[0]["PayType"].ToString() != "")
                {
                    ent.PayType = int.Parse(table.Rows[0]["PayType"].ToString());
                }
                ent.BankName = table.Rows[0]["BankName"].ToString();
                ent.BankNum = table.Rows[0]["BankNum"].ToString();
                if (table.Rows[0]["Birthday"].ToString() != "")
                {
                    ent.Birthday = DateTime.Parse(table.Rows[0]["Birthday"].ToString());
                }
                if (table.Rows[0]["Sex"].ToString() != "")
                {
                    ent.Sex = int.Parse(table.Rows[0]["Sex"].ToString());
                }
                ent.Addresses = table.Rows[0]["Addresses"].ToString();
                ent.Tel = table.Rows[0]["Tel"].ToString();
                ent.TIN = table.Rows[0]["TIN"].ToString();
                if (table.Rows[0]["ClearingType"].ToString() != "")
                {
                    ent.ClearingType = int.Parse(table.Rows[0]["ClearingType"].ToString());
                }
                if (table.Rows[0]["MaxMoney"].ToString() != "")
                {
                    ent.MaxMoney = decimal.Parse(table.Rows[0]["MaxMoney"].ToString());
                }
                ent.OtherTel = table.Rows[0]["OtherTel"].ToString();
                ent.Remark = table.Rows[0]["Remark"].ToString();
                if (table.Rows[0]["CreateMan"].ToString() != "")
                {
                    ent.CreateMan = int.Parse(table.Rows[0]["CreateMan"].ToString());
                }
                if (table.Rows[0]["UpdateTime"].ToString() != "")
                {
                    ent.UpdateTime = DateTime.Parse(table.Rows[0]["UpdateTime"].ToString());
                }
                if (table.Rows[0]["CumulativeMoney"].ToString() != "")
                {
                    ent.CumulativeMoney = decimal.Parse(table.Rows[0]["CumulativeMoney"].ToString());
                }
                if (table.Rows[0]["PaidMoney"].ToString() != "")
                {
                    ent.PaidMoney = decimal.Parse(table.Rows[0]["PaidMoney"].ToString());
                }
                if (table.Rows[0]["OutstMoney"].ToString() != "")
                {
                    ent.OutstMoney = decimal.Parse(table.Rows[0]["OutstMoney"].ToString());
                }
                if (table.Rows[0]["IsPrivacy"].ToString() != "")
                {
                    ent.IsPrivacy = int.Parse(table.Rows[0]["IsPrivacy"].ToString());
                }
                ent.WangWangNum = table.Rows[0]["WangWangNum"].ToString();
                if (table.Rows[0]["ConsumeNum"].ToString() != "")
                {
                    ent.ConsumeNum = int.Parse(table.Rows[0]["ConsumeNum"].ToString());
                }
                return ent;
            }
            return null;
        }

        public DataTable GetPageList(int PageSize, int PageIndex, string strWhere)
        {
            string orderString = "";
            int pageCount = 0;
            int recordCount = 0;
            StringBuilder builder = new StringBuilder();
            builder.Append("select * from T_Guest ");
            return DbHelperOledb.ExecutePager(this.dbname, PageIndex, PageSize, "", builder.ToString(), strWhere, orderString, out pageCount, out recordCount, "GuestId");
        }

        public DataTable SearchData(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select GuestId,GuestTypes,");
            builder.Append("GuestName,");
            builder.Append("(Select GuestName from T_GuestGrade WHERE T_Guest.GuestTypeId=T_GuestGrade.GuestTypeId) as GuestTypeName,");
            builder.Append("(select sum(ToMeetMoney) from T_Orders where T_Guest.GuestId=T_Orders.GuestId and T_Orders.IsOut=1) as ToMeetMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Orders where T_Guest.GuestId=T_Orders.GuestId and T_Orders.IsOut=1 and T_Orders.SaleVtag=2) as PaidMoney,");
            builder.Append("(select (sum(ToMeetMoney)-sum(PaidMoney)) from T_Orders where T_Guest.GuestId=T_Orders.GuestId ) as ArrearageMoney,");
            builder.Append("(select max(CreateTime) from T_Orders where T_Guest.GuestId=T_Orders.GuestId) as CreateTime");
            builder.Append(" from T_Guest");
            if (strWhere != "")
            {
                builder.Append(" where ");
                builder.Append(strWhere);
                builder.Append(" and ChainNum=" + ent.ChainNum);
            }
            else
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
            }
            DataTable table = DbHelperOledb.Query(this.dbname, builder.ToString());
            table.Columns.Add("GuestType");
            foreach (DataRow row in table.Rows)
            {
                try
                {
                    if (row["GuestTypes"].ToString() == "1")
                    {
                        row["GuestType"] = "个人";
                    }
                    else
                    {
                        row["GuestType"] = "公司";
                    }
                    row["ArrearageMoney"] = decimal.Parse(row["ToMeetMoney"].ToString()) - decimal.Parse(row["PaidMoney"].ToString());
                }
                catch (Exception)
                {
                }
            }
            return table;
        }

        public DataTable SearchData(int PageSize, int PageIndex, string strWhere)
        {
            int pageCount = 0;
            int recordCount = 0;
            StringBuilder builder = new StringBuilder();
            UserEnt ent = (UserEnt) WinformSession.GetSession()["user"];
            builder.Append("select GuestId,GuestTypes,");
            builder.Append("GuestName,");
            builder.Append("(Select GuestName from T_GuestGrade WHERE T_Guest.GuestTypeId=T_GuestGrade.GuestTypeId) as GuestTypeName,");
            builder.Append("(select sum(ToMeetMoney) from T_Orders where T_Guest.GuestId=T_Orders.GuestId and T_Orders.IsOut=1) as ToMeetMoney,");
            builder.Append("(select sum(ToMeetMoney) from T_Orders where T_Guest.GuestId=T_Orders.GuestId and T_Orders.IsOut=1 and T_Orders.SaleVtag=2) as PaidMoney,");
            builder.Append("(select (sum(ToMeetMoney)-sum(PaidMoney)) from T_Orders where T_Guest.GuestId=T_Orders.GuestId ) as ArrearageMoney,");
            builder.Append("(select max(CreateTime) from T_Orders where T_Guest.GuestId=T_Orders.GuestId) as CreateTime");
            builder.Append(" from T_Guest");
            if (strWhere != "")
            {
                builder.Append(" where ");
                builder.Append(strWhere);
                builder.Append(" and ChainNum=" + ent.ChainNum);
            }
            else
            {
                builder.Append(" where ChainNum=" + ent.ChainNum);
            }
            DataTable table = DbHelperOledb.ExecutePager(this.dbname, PageIndex, PageSize, "", builder.ToString(), strWhere, "$%", out pageCount, out recordCount, "GuestId");
            table.Columns.Add("GuestType");
            foreach (DataRow row in table.Rows)
            {
                try
                {
                    if (row["GuestTypes"].ToString() == "1")
                    {
                        row["GuestType"] = "个人";
                    }
                    else
                    {
                        row["GuestType"] = "公司";
                    }
                    row["ArrearageMoney"] = decimal.Parse(row["ToMeetMoney"].ToString()) - decimal.Parse(row["PaidMoney"].ToString());
                }
                catch (Exception)
                {
                }
            }
            return table;
        }

        public void Update(GuestEnt model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update T_Guest set ");
            builder.Append("ChainNum=" + model.ChainNum + ",");
            builder.Append("GuestTypes=" + model.GuestTypes + ",");
            builder.Append("GuestTypeId=" + model.GuestTypeId + ",");
            builder.Append("GuestName='" + model.GuestName + "',");
            builder.Append("PayType=" + model.PayType + ",");
            builder.Append("BankName='" + model.BankName + "',");
            builder.Append("BankNum='" + model.BankNum + "',");
            builder.Append("Birthday='" + model.Birthday + "',");
            builder.Append("Sex=" + model.Sex + ",");
            builder.Append("Addresses='" + model.Addresses + "',");
            builder.Append("Tel='" + model.Tel + "',");
            builder.Append("TIN='" + model.TIN + "',");
            builder.Append("ClearingType=" + model.ClearingType + ",");
            builder.Append("MaxMoney=" + model.MaxMoney + ",");
            builder.Append("OtherTel='" + model.OtherTel + "',");
            builder.Append("Remark='" + model.Remark + "',");
            builder.Append("CreateMan=" + model.CreateMan + ",");
            builder.Append("UpdateTime='" + model.UpdateTime + "',");
            builder.Append("CumulativeMoney=" + model.CumulativeMoney + ",");
            builder.Append("PaidMoney=" + model.PaidMoney + ",");
            builder.Append("OutstMoney=" + model.OutstMoney + ",");
            builder.Append("IsPrivacy=" + model.IsPrivacy + ",");
            builder.Append("WangWangNum='" + model.WangWangNum + "',");
            builder.Append("ConsumeNum=" + model.ConsumeNum + "");
            builder.Append(" where GuestId=" + model.GuestId + "");
            DbHelperOledb.ExecuteSql(this.dbname, builder.ToString());
        }
    }
}

